Kish_HP2

Author

John Kish

IMPORT AND SETUP

Code

library(tidyverse)
library(here)
library(scales)

Import

here::i_am("analysis/Kish_HP2.qmd")
library(here)

df <- read.csv(here("data/Employee_Salaries.csv"))

Data Interprtation

# Department
# Abbreivation/accronym for the department name. Example: ABS for the Alcohol Beverage Services department

# Department_Name
# the department name for all active, permanent employees of Montgomery County, MD (Maryland)


# Division
# The division for each department. That is, a single department can have multiple divisions and focuses.
# Example: ABS has the divisions that include those of ABS 85 Administrative Services
# and ABS 85 Beer Delivery Operations

# Gender
# the Gender of M and F. Assuming that since this is filings with the government, M is to be the legal sex of the individual M for Male and F for Female.

# Base_Salary
# precise base salaries (including down to the cent) for the individual, at the end of the calendar year.
# this is projected data. Should be accurate, but projection allows for greater flexibiliity.


# Overtime_Pay
# amount of overtime pay an employee/individual received for working beyond what they were required to do


# Longevity_Pay
# Longevity pay gives additional compensation for qualifying employees who have worked for a good deal of time. The longer time that is worked, the greater the longevity pay. Specifics about how long are dealt with in-house.


# Grade
# the standing an employee has within a company. The internal codes for all positions are not provided, but we can safely look at M1, M2, M3, etc. as those are clearly levels of management. There are thousands of grades.

# M4-Senior Manager
# M3-Middle Manager
# M2-Middle Manager
# M1-First Level Manager

BACKGROUND

employees of Montgomery County, MD paid in calendar year 2023

PRELIMINARY ANALYSIS

Total Compensation Additional Compensation no missing values using sex and not gender

df$Total_Compensation <- df$Base_Salary + df$Overtime_Pay + df$Longevity_Pay
df$Additional_Compensation <- df$Overtime_Pay + df$Longevity_Pay

df <- rename(df, Sex = "Gender")
# str(df)
# df <- df |> mutate(across(where(is.double), as.integer))

sum(is.na(df))

[1] 0

# No NA Values

How many employees are there? How many of them get overtime, longevity, either, or both?

Question 1 Total Compensation and Overtime

Preliminary Analysis

#each row is a unique employee
Total_Employees <- nrow(df)
Employees_Without_Base <- filter(df, Base_Salary == 0.00) |> nrow()
df <- mutate(df, Employee = row_number())



Employees_Without_Overtime <- filter(df, Overtime_Pay == 0.00)
#4,651 employees got no overtime
Employees_With_Overtime <- anti_join(df, Employees_Without_Overtime)
Percent_Overtime = (nrow(Employees_With_Overtime) / Total_Employees) * 100
# 54% ish employees have overtime


Employees_Without_Longevity <- filter(df, Longevity_Pay == 0.00)
#7,454 employees got no longevity pay
Employees_With_Longevity <- anti_join(df, Employees_Without_Longevity)
Percent_Longevity = (nrow(Employees_With_Longevity) / Total_Employees) * 100
# 27% is of employees have longevity pay



Employees_Without_Over_Long <- filter(df, Overtime_Pay == 0.00 | Longevity_Pay == 0.00)
# 3491 employees have no overtime or longevity pay 
Employees_With_Over_Long <- anti_join(df, Employees_Without_Over_Long)
Percent_Over_Long <- (nrow(Employees_With_Over_Long) / Total_Employees) * 100
# 66% percent of employees have overtime or longevity pay


Employees_Without_Add_Comp <- filter(df, Additional_Compensation == 0.00)
# 3491 employees have no overtime or longevity pay 
Employees_With_Add_Comp <- anti_join(df, Employees_Without_Add_Comp)
Percent_Add_Comp <- (nrow(Employees_With_Add_Comp) / Total_Employees) * 100
# 66% percent of employees have overtime or longevity pay

There are 10291 employees total, where 0 employees have no base salary

Approximately 16.3% have overtime pay, 27.6% have longevity pay, and 16.3% have one or the either.

How much of their total compensation is base + overtime, longevity, either, or both (In terms of percentage?)

Respective Percentage Total Compensation

# addiing to the original df a new column, using mutate, that is the percent of total
# compensation that is each of the relevant factors.

df <- group_by(df, Employee) |>
  mutate(Percent_Over = Overtime_Pay/Total_Compensation)
df$Percent_Over <- round(df$Percent_Over, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Long = Longevity_Pay/Total_Compensation)
df$Percent_Long <- round(df$Percent_Long, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Base = Base_Salary/Total_Compensation)
df$Percent_Base <- round(df$Percent_Base, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Add_Comp = Additional_Compensation/Total_Compensation)
df$Percent_Add_Comp <- round(df$Percent_Add_Comp, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Over_Long = Base_Salary/Total_Compensation)
df$Percent_Over_Long <- round(df$Percent_Over_Long, 3) * 100

How much of their total compensation is base + overtime, longevity, either, or both?

Preliminary Analysis Plots

#overall
p <- df |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Employees") +
  geom_smooth(method = "lm")
p

Preliminary Analysis Plots

p2 <- Employees_With_Overtime |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Those with Overtime Pay") +
  geom_smooth(method = "lm")
p2

Preliminary Analysis Plots

p3 <- Employees_With_Longevity |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Those with Longevity Pay") +
  geom_smooth(method = "lm")
p3

Preliminary Analysis Plots

p4 <- Employees_With_Over_Long |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Those with Overtime or longevity pay") +
  geom_smooth(method = "lm")
p4

Preliminary Analysis Plots

p5 <- Employees_With_Add_Comp |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All those with additional compensation") +
  geom_smooth(method = "lm")
p5

the coeff for these graphs